package com.hualan.mapper;

import com.hualan.pojo.*;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface ConsultantsMapper {
    @Select(value = "select * from consultants where consultants.del=0")
    @Results({
            @Result(property = "id",column = "id",id = true),
            @Result(
                    property = "consultantLevels",
                    column = "level_id",
                    one = @One(select = "findById"))
    })
    List<Consultants> listAll();

    @Select("select level_name from consultant_levels where consultant_levels.del=0 and id = #{id}")
    ConsultantLevels findById(Integer id);

    @Select("select * from consultants where consultants.del=0  and id = #{id}")
    Consultants findById2(Integer id);

    @Select("select shanchang.* from shanchang join shanchang_consultant on shanchang.id= shanchang_consultant.shanchang_id where shanchang.del=0 and shanchang_consultant.consultant_id=#{id}")
    List<Shanchang> findAll(Integer id);

    @Select("select shoufei.* from shoufei join shoufei_fenlei on shoufei.id= shoufei_fenlei.shoufei_id where shoufei_fenlei.consultant_id=#{id}")
    List<Shoufei> listAllShoufei(Integer id);


    @Select(value = "select * from huifu where huifu.del=0 and tid=#{id}")
    @Results({
            @Result(property = "id",column = "id",id = true),
            @Result(
                    property = "problem",
                    column = "wid",
                    many = @Many(select = "findByIdHuifu"))
    })
    List<Huifu> listAllHuifu(Integer id);

    @Select("select * from problem where id = #{id}")
    Problem findByIdHuifu(Integer id);

    @Select("select * from userpinglun where userpinglun.del=0 and tid=#{id}")
    List<Userpinglun> findByIdUserpinglun(Integer id);

    @Select(value = "select * from fabukecheng where fabukecheng.del=0 and tid=#{id}")
    List<Fabukecheng> findByIdFabukecheng(Integer id);


    // 模糊查询专家信息，避免重复数据，返回专家信息及其擅长领域
    @Select("SELECT DISTINCT " +
            "c.id AS consultant_id, " +
            "c.name AS consultant_name, " +
            "c.img AS consultant_img, " +  // 专家图片
            "c.location AS consultant_location, " +
            "c.jieshao AS consultant_jieshao, " +
            "cl.level_name AS consultant_level " +
            "FROM consultants c " +
            "JOIN consultant_levels cl ON c.level_id = cl.id " +
            "LEFT JOIN shanchang_consultant sc ON c.id = sc.consultant_id " +
            "LEFT JOIN shanchang s ON sc.shanchang_id = s.id " +
            "WHERE c.name LIKE CONCAT('%', #{keyword}, '%') " +
            "AND (s.del != 1 OR s.del IS NULL)")
    @Results({
            @Result(property = "id", column = "consultant_id"),
            @Result(property = "name", column = "consultant_name"),
            @Result(property = "img", column = "consultant_img"),
            @Result(property = "location", column = "consultant_location"),
            @Result(property = "jieshao", column = "consultant_jieshao"),
            @Result(property = "consultantLevels.levelName", column = "consultant_level"),
            @Result(property = "shanchangList", column = "consultant_id", many = @Many(select = "findShanchangById"))
    })
    List<Consultants> searchConsultants(@Param("keyword") String keyword);

    // 查询擅长领域信息
    @Select("SELECT s.id AS shanchang_id, " +
            "s.name AS shanchang_name " +
            "FROM shanchang s " +
            "JOIN shanchang_consultant sc ON s.id = sc.shanchang_id " +
            "WHERE sc.consultant_id = #{id} AND (s.del != 1 OR s.del IS NULL)")
    @Results({
            @Result(property = "id", column = "shanchang_id"),
            @Result(property = "name", column = "shanchang_name")
    })
    List<Shanchang> findShanchangById(Integer id);

}
